﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Document_Management
{
    public partial class frm_cuttinglingyongjilu : Form
    {
        static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["庫存管理"].ConnectionString;
        string flag,tb0,tb1, tb2, tb3, tb4, tb5, tb6, str;
        public frm_cuttinglingyongjilu()
        {
            InitializeComponent();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        
        public void Load_刀具_lingyongjilu()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter("sp_刀具_lingyong_load", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                da.Fill(dt);
                //this.Size = new System.Drawing.Size(this.Size.Width, height - (gBAdd新增.Height + 13));
                data_領用.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                data_領用.DataSource = dt;
                PublicClass.setRowNumber(data_領用);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
        private void frm_cuttinglingyongjilu_Load(object sender, EventArgs e)
        {
            Load_刀具_lingyongjilu();
        }

        private void btnTra_Click(object sender, EventArgs e)
        {
            try
            {
                int r = data_領用.CurrentCell.RowIndex;
                string id0 = data_領用.Rows[r].Cells[1].Value.ToString();
                string id1 = data_領用.Rows[r].Cells[0].Value.ToString();
                string id2 = data_領用.Rows[r].Cells[2].Value.ToString();
                string id3 = data_領用.Rows[r].Cells[3].Value.ToString();
                string id4 = data_領用.Rows[r].Cells[4].Value.ToString();
                string id5 = data_領用.Rows[r].Cells[5].Value.ToString();
                string id6 = data_領用.Rows[r].Cells[6].Value.ToString();
                tb0 = id0;
                tb1 = id1; //cb
                tb2 = id2;
                tb3 = id3;
                tb4 = id4;
                tb5 = id5;
                tb6 = id6;
                
                frmcuttingguihuan frm = new frmcuttingguihuan(flag, tb0, tb1, tb2, tb3, tb4, tb5, tb6);
                frm.ShowDialog();
                Load_刀具_lingyongjilu();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //frmcuttingguihuan frm = new frmcuttingguihuan();
            //frm.ShowDialog();
        }

        private void btnSearch領用_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                string sp = "sp_刀具領用_尋找";
                SqlCommand cmd = new SqlCommand(sp, con);
                cmd.CommandType = CommandType.StoredProcedure;
                if (txt_刀具編號_seach.Text != "") cmd.Parameters.AddWithValue("@刀具編號", txt_刀具編號_seach.Text);
                cmd.Parameters.AddWithValue("@領用日期記錄From", date_From_領用日期.Value);
                cmd.Parameters.AddWithValue("@領用日期記錄To", date_To_領用日期.Value);
                cmd.Parameters.AddWithValue("@歸還日期記錄From", date_from_歸還日期.Value);
                cmd.Parameters.AddWithValue("@歸還日期記錄To", date_To_歸還日期.Value);
                //if (date_From_領用日期.Text != "") cmd.Parameters.AddWithValue("@領用日期", DateTime.Parse(date_From_領用日期.Text));
                //if (date_from_歸還日期.Text != "") cmd.Parameters.AddWithValue("@歸還日期", DateTime.Parse(date_from_歸還日期.Text));
                
                SqlDataReader sqlreader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                //dt.Columns.Add("項次");//add new 項次 column
                dt.Columns.Add("刀具編號");
                dt.Columns.Add("領用日期");
                dt.Columns.Add("歸還日期");
                //int k=1;
                while (sqlreader.Read())
                {
                    DataRow dtrows = dt.NewRow();
                    //dtrows["項次"]=k.ToString();
                    dtrows["刀具編號"] = sqlreader["刀具編號"];
                    dtrows["領用日期"] = sqlreader["領用日期"];
                    dtrows["歸還日期"] = sqlreader["歸還日期"];
                    dt.Rows.Add(dtrows);
                    //k++;
                }
                data_領用.DataSource = dt;
                PublicClass.setRowNumber(data_領用);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // cb_年份.Checked = false;
            date_From_領用日期.Text = "";
            date_from_歸還日期.Text = "";
            txt_刀具編號_seach.Text = "";
           
            //cb_車輛_預保日.Checked = false;
            Load_刀具_lingyongjilu();
        }

        private void bt_刀具領用部門_新增_Click(object sender, EventArgs e)
        {
            try
            {
                if (txt_部門代號.Text.ToString() == null || txt_部門代號.Text.ToString().Equals(""))
                    MessageBox.Show("部門代號還沒輸入資料");
                else
                {
                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    SqlCommand cmd0;
                    String sql = "SELECT COUNT(*) FROM 刀具領用部門 WHERE 刀具領用部門代號='" + txt_部門代號.Text.ToString() + "'";
                    cmd0 = new SqlCommand(sql, con);
                    if ((int)cmd0.ExecuteScalar() > 0)
                    {
                        MessageBox.Show("Error! '" + txt_部門代號.Text.ToString() + "' 由來");
                    }
                    else
                    {
                        cmd0 = new SqlCommand("sp_刀具領用部門_insert", con);
                        cmd0.CommandType = CommandType.StoredProcedure;
                        cmd0.Parameters.AddWithValue("@部門代號", txt_部門代號.Text.ToString());
                        cmd0.Parameters.AddWithValue("@部門名稱", txt_部門名稱.Text.ToString());
                        cmd0.Parameters.AddWithValue("@備註", txt_備註.Text.ToString());
                        cmd0.ExecuteNonQuery();
                        MessageBox.Show("新增成功");
                        Load_刀具領用部門();
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
            //Load_cbx領用部門();
        }

        private void bt_刀具領用部門_修改_Click(object sender, EventArgs e)
        {
            try
            {
                if (txt_部門代號.Text.ToString() == null || txt_部門代號.Text.ToString().Equals(""))
                    MessageBox.Show("部門代號還沒輸入資料");
                else
                {
                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    SqlCommand cmd0;
                    String sql = "SELECT COUNT(*) FROM 刀具領用部門 WHERE 刀具領用部門代號='" + str + "'";
                    cmd0 = new SqlCommand(sql, con);
                    if ((int)cmd0.ExecuteScalar() > 0)
                    {
                        cmd0 = new SqlCommand("sp_刀具領用部門_update", con);
                        cmd0.CommandType = CommandType.StoredProcedure;
                        cmd0.Parameters.AddWithValue("@部門代號_1", str);
                        cmd0.Parameters.AddWithValue("@部門代號_2", txt_部門代號.Text.ToString());
                        cmd0.Parameters.AddWithValue("@部門名稱", txt_部門名稱.Text.ToString());
                        cmd0.Parameters.AddWithValue("@備註", txt_備註.Text.ToString());
                        cmd0.ExecuteNonQuery();
                        MessageBox.Show("修改成功");
                        Load_刀具領用部門();
                    }
                    else
                    {
                        MessageBox.Show("Error! '" + str + "' 不由來");
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
        }

        private void bt_刀具領用部門_刪除_Click(object sender, EventArgs e)
        {
            try
            {
                if (txt_部門代號.Text.ToString() == null || txt_部門代號.Text.ToString().Equals(""))
                    MessageBox.Show("部門代號還沒輸入資料");
                else
                {
                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    SqlCommand cmd0;
                    String sql = "SELECT COUNT(*) FROM 刀具領用部門 WHERE 刀具領用部門代號='" + txt_部門代號.Text.ToString() + "'";
                    cmd0 = new SqlCommand(sql, con);
                    if ((int)cmd0.ExecuteScalar() > 0)
                    {
                        cmd0 = new SqlCommand("sp_刀具領用部門_delete", con);
                        cmd0.CommandType = CommandType.StoredProcedure;
                        cmd0.Parameters.AddWithValue("@部門代號", txt_部門代號.Text.ToString());
                        cmd0.ExecuteNonQuery();
                        MessageBox.Show("刪除成功");
                        Load_刀具領用部門();
                    }
                    else
                    {
                        MessageBox.Show("Error! '" + txt_部門代號.Text.ToString() + "' 不由來");
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
        }

        private void bt_刀具領用部門_取消_Click(object sender, EventArgs e)
        {
            txt_部門代號.Text = "";
            txt_部門名稱.Text = "";
            txt_備註.Text = "";
            Load_刀具領用部門();
        }

        public void Load_刀具領用部門()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter("sp_刀具領用部門_load", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGrid_刀具領用部門.DataSource = dt;
                PublicClass.setRowNumber(dataGrid_刀具領用部門);
                dataGrid_刀具領用部門.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                con.Close();
                txt_部門代號.Text = "";
                txt_部門名稱.Text = "";
                txt_備註.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
                return;
            }
        }

        private void bt_刀具領用人_新增_Click(object sender, EventArgs e)
        {
            try
            {
                if (txt_領用人代號.Text.ToString() == null || txt_領用人代號.Text.ToString().Equals(""))
                    MessageBox.Show("領用人代號還沒輸入資料");
                else
                {
                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    SqlCommand cmd0;
                    String sql = "SELECT COUNT(*) FROM 刀具領用人 WHERE 刀具領用人代號='" + txt_領用人代號.Text.ToString() + "'";
                    cmd0 = new SqlCommand(sql, con);
                    if ((int)cmd0.ExecuteScalar() > 0)
                    {
                        MessageBox.Show("Error! '" + txt_領用人代號.Text.ToString() + "' 由來");
                    }
                    else
                    {
                        cmd0 = new SqlCommand("sp_刀具領用人_insert", con);
                        cmd0.CommandType = CommandType.StoredProcedure;
                        cmd0.Parameters.AddWithValue("@領用人代號", txt_領用人代號.Text.ToString());
                        cmd0.Parameters.AddWithValue("@領用人名稱", txt_領用人姓名.Text.ToString());
                        cmd0.Parameters.AddWithValue("@領用部門代號", cbx領用部門.Text.ToString());
                        cmd0.Parameters.AddWithValue("@電話", txt_電話.Text.ToString());
                        cmd0.ExecuteNonQuery();
                        MessageBox.Show("新增成功");
                        Load_刀具領用人();
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
        }

        private void bt_刀具領用人_修改_Click(object sender, EventArgs e)
        {
            try
            {
                if (txt_領用人代號.Text.ToString() == null || txt_領用人代號.Text.ToString().Equals(""))
                    MessageBox.Show("領用人代號還沒輸入資料");
                else
                {
                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    SqlCommand cmd0;
                    String sql = "SELECT COUNT(*) FROM 刀具領用人 WHERE 刀具領用人代號='" + str + "'";
                    cmd0 = new SqlCommand(sql, con);
                    if ((int)cmd0.ExecuteScalar() > 0)
                    {
                        cmd0 = new SqlCommand("sp_刀具領用人_update", con);
                        cmd0.CommandType = CommandType.StoredProcedure;
                        cmd0.Parameters.AddWithValue("@領用人代號_1", str);
                        cmd0.Parameters.AddWithValue("@領用人代號_2", txt_領用人代號.Text.ToString());
                        cmd0.Parameters.AddWithValue("@領用人名稱", txt_領用人姓名.Text.ToString());
                        cmd0.Parameters.AddWithValue("@領用部門代號", cbx領用部門.Text.ToString());
                        cmd0.Parameters.AddWithValue("@電話", txt_電話.Text.ToString());
                        cmd0.ExecuteNonQuery();
                        MessageBox.Show("修改成功");
                        Load_刀具領用人();
                    }
                    else
                    {
                        MessageBox.Show("Error! '" + str + "' 不由來");
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
        }

        private void bt_刀具領用人_刪除_Click(object sender, EventArgs e)
        {
            try
            {
                if (txt_領用人代號.Text.ToString() == null || txt_領用人代號.Text.ToString().Equals(""))
                    MessageBox.Show("領用人代號還沒輸入資料");
                else
                {
                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    SqlCommand cmd0;
                    String sql = "SELECT COUNT(*) FROM 刀具領用人 WHERE 刀具領用人代號='" + str + "'";
                    cmd0 = new SqlCommand(sql, con);
                    if ((int)cmd0.ExecuteScalar() > 0)
                    {
                        cmd0 = new SqlCommand("sp_刀具領用人_delete", con);
                        cmd0.CommandType = CommandType.StoredProcedure;
                        //cmd0.Parameters.AddWithValue("@領用人代號_1", str);
                        cmd0.Parameters.AddWithValue("@領用人代號", txt_領用人代號.Text.ToString());
                        //cmd0.Parameters.AddWithValue("@領用人姓名", txt_領用人姓名.Text.ToString());
                        //cmd0.Parameters.AddWithValue("@領用部門代號", cbx領用部門.Text.ToString());
                        cmd0.ExecuteNonQuery();
                        MessageBox.Show("刪除成功");
                        Load_刀具領用人();
                    }
                    else
                    {
                        MessageBox.Show("Error! '" + str + "' 不由來");
                    }
                    con.Close();
                }
            }catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
        }

        private void bt_刀具領用人_取消_Click(object sender, EventArgs e)
        {
            txt_領用人代號.Text = "";
            txt_領用人姓名.Text = "";
            cbx領用部門.Text = "";
            txt_電話.Text = "";
            Load_刀具領用人();
        }

        public void Load_刀具領用人()
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter("sp_刀具領用人_load", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGrid_刀具領用人.DataSource = dt;
                PublicClass.setRowNumber(dataGrid_刀具領用人);
                dataGrid_刀具領用人.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                con.Close();
                txt_領用人代號.Text = "";
                txt_領用人姓名.Text = "";
                cbx領用部門.Text = "";
                txt_電話.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        private void tabControl_刀具領用_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl_刀具領用.SelectedIndex == 0)
            {
                Load_刀具_lingyongjilu();
                PublicClass.setRowNumber(data_領用);
            }
            if (tabControl_刀具領用.SelectedIndex == 1)
            {
                Load_刀具領用人();
                PublicClass.setRowNumber(dataGrid_刀具領用人);
            }
            if (tabControl_刀具領用.SelectedIndex == 2)
            {
                Load_刀具領用部門();
                PublicClass.setRowNumber(dataGrid_刀具領用部門);
            }
        }

        //public void Load_cbx領用部門()
        //{
        //    SqlConnection connection = new SqlConnection(connectionString);
        //    connection.Open();
        //    string sqlQuery = "SELECT * FROM 刀具領用部門";
        //    SqlCommand command = new SqlCommand(sqlQuery, connection);
        //    SqlDataReader sqlreader = command.ExecuteReader();
        //    while (sqlreader.Read())
        //    {
        //        cbx領用部門.Items.Add(sqlreader["刀具領用部門代號"].ToString());
        //    }
        //}

        private void dataGrid_刀具領用部門_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            DataGridViewRow row = new DataGridViewRow();
            row = dataGrid_刀具領用部門.Rows[e.RowIndex];
            txt_部門代號.Text = row.Cells[0].Value.ToString();
            str = row.Cells[0].Value.ToString();
            txt_部門名稱.Text = row.Cells[1].Value.ToString();
            txt_備註.Text = row.Cells[2].Value.ToString();
        }

        private void dataGrid_刀具領用人_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            DataGridViewRow row = new DataGridViewRow();
            row = dataGrid_刀具領用人.Rows[e.RowIndex];
            txt_領用人代號.Text = row.Cells[0].Value.ToString();
            str = row.Cells[0].Value.ToString();
            txt_領用人姓名.Text = row.Cells[1].Value.ToString();
            cbx領用部門.Text = row.Cells[2].Value.ToString();
            txt_電話.Text = row.Cells[3].Value.ToString();
        }

        private void cbx領用部門_TextChanged(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                String temp = "";
                string strcmd = String.Format("select * from 刀具領用部門 where 刀具領用部門代號=N'{0}'", cbx領用部門.Text);
                SqlCommand cmd = new SqlCommand(strcmd, con);
                var x = cmd.ExecuteReader();
                while (x.Read()) temp = x[1].ToString();
                if (temp == "") lb_領用部門.Text = "";
                else lb_領用部門.Text = temp;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error!");
                con.Close();
            }
        }

        private void bt_領用部門_search_Click(object sender, EventArgs e)
        {
            Cuttingtool_manager_seach1 frm = new Cuttingtool_manager_seach1(cbx領用部門);
            frm.ShowDialog();
        }
    }
}
